package com.example.wyh.intelcheckbash.sqlite;

import android.content.Context;
import android.content.pm.PackageInfo;
import android.content.pm.PackageManager;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class TaskDbHelper extends SQLiteOpenHelper {
    private static final String TAG = "DatabaseHelper";
    private static final String DB_NAME = "check_bash";//数据库名字
    public static String bc = "bc";// 列名
    private static String user_id = "user_id";
    public static String ID = "id";
    public static String task_id = "task_id";// 列名
    public static String area_id = "area_id";// 列名
    public static String equipment_id = "equipment_id";// 列名
    public static String task_data = "data";// 列名
    public static String is_complete = "is_complete";
    public static String date = "time";// 列名
    public static String isUploadSuccess = "isUploadSuccess";
    public static String status = "equipmentStatus";
    private static final int DB_VERSION = 10;   // 数据库版本


    public TaskDbHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    /**
     * 创建数据库
     *
     * @param db
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        //创建表
        String sql = "CREATE TABLE " + "task" + "(" + ID + " integer primary key autoincrement , "
                + task_id + " text not null,"
                + user_id + " text not null,"
                + bc + " text not null,"
                + task_data + " text not null,"
                + is_complete + " boolean not null,"
                + "start_date" + " text,"
                + "end_date" + " text,"
                + date + " text not null);";
        try {
            db.execSQL(sql);
        } catch (SQLException e) {
            Log.e(TAG, "onCreate " + "task" + "Error" + e.toString());
        }

        String sql1 = "CREATE TABLE " + "complete_task" + "(" + ID + " integer primary key autoincrement , "
                + task_id + " text not null,"
                + area_id + " text not null,"
                + equipment_id + " text not null,"
                + user_id + " text not null,"
                + isUploadSuccess + " text not null,"
                + status + " text not null,"
                + "task_info_type" + " text not null,"
                + task_data + " text not null," + date + " text not null);";
        try {
            db.execSQL(sql1);
        } catch (SQLException e) {
            Log.e(TAG, "onCreate " + "complete_task" + "Error" + e.toString());
        }


        String bug_list_sql = "CREATE TABLE " + "bug_list" + "(" + ID + " integer primary key autoincrement , "
                + user_id + " text not null,"
                + "data" + " text not null,"
                + "date" + " text not null,"
                + "qxxtm" + " text not null,"
                + "find_time" + " text not null);";
        try {
            db.execSQL(bug_list_sql);
        } catch (SQLException e) {
            Log.e(TAG, "onCreate " + "bug_list" + "Error" + e.toString());
        }

        String product_list_sql = "CREATE TABLE " + "product_list" + "(" + ID + " integer primary key autoincrement , "
                + user_id + " text not null,"
                + "data" + " text not null,"
                + "xsxtm" + " text not null,"
                + "date" + " text not null,"
                + "isUpload" + " text not null);";
        try {
            db.execSQL(product_list_sql);
        } catch (SQLException e) {
            Log.e(TAG, "onCreate " + "product_list_sql" + "Error" + e.toString());
        }

        //创建生产巡视列表
//        String product_list_sql = "CREATE TABLE " + "product_list" + "(" + ID + " integer primary key autoincrement , "
//                + user_id + " text not null,"
//                + "data" + " text not null,"
//                + "xsxtm" + " text not null,"
//                + "date" + " text not null,"
//                + "isUpload" + " text not null);";
//        try {
//            db.execSQL(product_list_sql);
//        } catch (SQLException e) {
//            Log.e(TAG, "onCreate " + "product_list_sql" + "Error" + e.toString());
//        }

    }

    /**
     * 数据库升级
     *
     * @param db
     * @param oldVersion
     * @param newVersion
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        Log.e("newVersion:oldVersion＝＝＝＝＝＝＝＝",newVersion+":"+oldVersion);

        if (newVersion>oldVersion){
            db.execSQL("ALTER TABLE "+"task"+" RENAME TO "+"task"+"_temp");
            db.execSQL("CREATE TABLE " + "task" + "(" + ID + " integer primary key autoincrement , "
                    + task_id + " text not null,"
                    + user_id + " text not null,"
                    + bc + " text not null,"
                    + task_data + " text not null,"
                    + is_complete + " boolean not null,"
                    + "start_date" + " text,"
                    + "end_date" + " text,"
                    + date + " text not null);");

            db.execSQL("insert into task("+
                    ID+", "
                    +task_id+", "
                    +user_id+", "
                    +bc+", "
                    +task_data+", "
                    +is_complete+", "
                    +date+") "
                    + "select "
                    +ID+", "
                    +task_id+", "
                    +user_id+", "
                    +bc+", "
                    +task_data+", "
                    +is_complete+", "
                    +date + " from "+"task"+"_temp");
            db.execSQL("DROP TABLE "+"task"+"_temp");



//            //创建生产巡视列表
//            String product_list_sql = "CREATE TABLE " + "product_list" + "(" + ID + " integer primary key autoincrement , "
//                    + user_id + " text not null,"
//                    + "data" + " text not null,"
//                    + "xsxtm" + " text not null,"
//                    + "date" + " text not null,"
//                    + "isUpload" + " text not null);";
//            try {
//                db.execSQL(product_list_sql);
//            } catch (SQLException e) {
//                Log.e(TAG, "onCreate " + "product_list_sql" + "Error" + e.toString());
//            }


            boolean isExistTableProductList = false;
            Cursor cursor = db.rawQuery("select name from sqlite_master where type='table' order by name",null);
            while(cursor.moveToNext()){
                //遍历出表名
                String name = cursor.getString(0);
                if (name.equals("product_list"))
                {
                    isExistTableProductList = true;
                }
            }

            if (isExistTableProductList == false)
            {
                String product_list_sql = "CREATE TABLE " + "product_list" + "(" + ID + " integer primary key autoincrement , "
                        + user_id + " text not null,"
                        + "data" + " text not null,"
                        + "xsxtm" + " text not null,"
                        + "date" + " text not null,"
                        + "isUpload" + " text not null);";
                try {
                    db.execSQL(product_list_sql);
                } catch (SQLException e) {
                    Log.e(TAG, "onCreate " + "product_list_sql" + "Error" + e.toString());
                }
            }
        }



//        if (newVersion>oldVersion){
//            db.execSQL("ALTER TABLE task RENAME TO task_temp");
//            db.execSQL("CREATE TABLE task(_id integer primary key"
//                    + "autoincrement, region varchar, code varchar, "
//                    + "country varchar)");
//            db.execSQL("insert into A(_id, region, code, country) "
//                    + "select _id, region, code, \"CHINA\" from A_temp");
//            db.execSQL("DROP TABLE A_temp");
//        }

    }

    public void init() {

    }

}